var fs = require('fs');
var _ = require('underscore');
var Promise = require('bluebird');

var Excel = require('../excel');

var Workbook = Excel.Workbook;
var WorkbookWriter = Excel.stream.xlsx.WorkbookWriter;

var filename = process.argv[2];

var wb = new WorkbookWriter({filename: filename, useSharedStrings: false, useStyles: false});
var ws = wb.addWorksheet('blort');

var fonts = {
  arialBlackUI14: { name: 'Arial Black', family: 2, size: 14, underline: true, italic: true },
  comicSansUdB16: { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true }
};

var alignments = [
  { text: 'Top Left', alignment: { horizontal: 'left', vertical: 'top' } },
  { text: 'Middle Centre', alignment: { horizontal: 'center', vertical: 'middle' } },
  { text: 'Bottom Right', alignment: { horizontal: 'right', vertical: 'bottom' } },
  { text: 'Wrap Text', alignment: { wrapText: true } },
  { text: 'Indent 1', alignment: { indent: 1 } },
  { text: 'Indent 2', alignment: { indent: 2 } },
  { text: 'Rotate 15', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 15 } },
  { text: 'Rotate 30', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 30 } },
  { text: 'Rotate 45', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 45 } },
  { text: 'Rotate 60', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 60 } },
  { text: 'Rotate 75', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 75 } },
  { text: 'Rotate 90', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 90 } },
  { text: 'Rotate -15', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -55 } },
  { text: 'Rotate -30', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -30 } },
  { text: 'Rotate -45', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -45 } },
  { text: 'Rotate -60', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -60 } },
  { text: 'Rotate -75', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -75 } },
  { text: 'Rotate -90', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -90 } },
  { text: 'Vertical Text', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 'vertical' } }
];
var badAlignments = [
  { text: 'Rotate -91', alignment: { textRotation: -91 } },
  { text: 'Rotate 91', alignment: { textRotation: 91 } },
  { text: 'Indent -1', alignment: { indent: -1 } },
  { text: 'Blank', alignment: {  } }
];

var borders = {
  thin: { top: {style:'thin'}, left: {style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'}},
  doubleRed: { color: {argb:'FFFF0000'}, top: {style:'double'}, left: {style:'double'}, bottom: {style:'double'}, right: {style:'double'}},
  thickRainbow: {
    top: {style:'double', color: {argb:'FFFF00FF'}},
    left: {style:'double', color: {argb:'FF00FFFF'}},
    bottom: {style:'double', color: {argb:'FF00FF00'}},
    right: {style:'double', color: {argb:'FF00FF'}},
    diagonal: {style:'double', color: {argb:'FFFFFF00'}, up: true, down: true}
  }
};

var fills = {
  redDarkVertical: {type: 'pattern', pattern:'darkVertical', fgColor:{argb:'FFFF0000'}},
  redGreenDarkTrellis: {type: 'pattern', pattern:'darkTrellis', fgColor:{argb:'FFFF0000'}, bgColor:{argb:'FF00FF00'}},
  blueWhiteHGrad: {type: 'gradient', gradient: 'angle', degree: 0,
    stops: [{position:0, color:{argb:'FF0000FF'}},{position:1, color:{argb:'FFFFFFFF'}}]},
  rgbPathGrad: {type: 'gradient', gradient: 'path', center:{left:0.5,top:0.5},
    stops: [{position:0, color:{argb:'FFFF0000'}},{position:0.5, color:{argb:'FF00FF00'}},{position:1, color:{argb:'FF0000FF'}}]}
};

ws.columns = [
  { header: 'Col 1', key:'key', width: 25 },
  { header: 'Col 2', key:'name', width: 32 },
  { header: 'Col 3', key:'age', width: 21 },
  { header: 'Col 4', key:'addr1', width: 18 },
  { header: 'Col 5', key:'addr2', width: 8 },
  { header: 'Col 6', width: 8 },
  { header: 'Col 7', width: 8 },
  { header: 'Col 8', width: 8, style: { font: fonts.comicSansUdB16, alignment: alignments[1].alignment } }
];

ws.getCell('A2').value = 7;
ws.getCell('B2').value = 'Hello, World!';
ws.getCell('B2').font = fonts.comicSansUdB16;
ws.getCell('B2').border = borders.thin;

ws.getCell('C2').value = -5.55;
ws.getCell('C2').numFmt = '"£"#,##0.00;[Red]\-"£"#,##0.00';
ws.getCell('C2').font = fonts.arialBlackUI14;

ws.getCell('D2').value = 3.14;
ws.getCell('D2').value = new Date();
ws.getCell('D2').numFmt = 'd-mmm-yyyy';
ws.getCell('D2').font = fonts.comicSansUdB16;
ws.getCell('D2').border = borders.doubleRed;

ws.getCell('E2').value = ['Hello', 'World'].join(', ') + '!';
ws.getRow(2).commit();


ws.getCell('A3').value = {text: 'www.google.com', hyperlink:'http://www.google.com'};
ws.getCell('A4').value = 'Boo!';
ws.getCell('C4').value = 'Hoo!';
ws.mergeCells('A4', 'C4');
ws.getRow(4).commit();

ws.getCell('A5').value = 1;
ws.getCell('B5').value = 2;
ws.getCell('C5').value = {formula: 'A5+B5', result:3};
ws.getRow(5).commit();

ws.getCell('A6').value = 'Hello';
ws.getCell('B6').value = 'World';
ws.getCell('C6').value = {formula:'CONCATENATE(A6,", ",B6,"!")', result:'Hello, World!'};
ws.getCell('C6').border = borders.thickRainbow;
ws.getRow(6).commit();

ws.getCell('A7').value = 1;
ws.getCell('B7').value = 2;
ws.getCell('C7').value = {formula:'A7+B7'};
ws.getRow(7).commit();

var now = new Date();
ws.getCell('A8').value = now;
ws.getCell('B8').value = 0;
ws.getCell('C8').value = {formula:'A8+B8', result: now};
ws.getRow(8).commit();

ws.getCell('A9').value = 1.6;
ws.getCell('A9').numFmt = '# ?/?';
ws.getCell('B9').value = 1.6;
ws.getCell('B9').numFmt = 'h:mm:ss';
ws.getCell('C9').value = 0.016;
ws.getCell('C9').numFmt = '0.00%';
ws.getCell('D9').value = 1.6;
ws.getCell('D9').numFmt = '[Green]#,##0 ;[Red](#,##0)';
ws.getCell('E9').value = 1.6;
ws.getCell('E9').numFmt = '#0.000;
ws.getCell('F9').value = 0.016;
ws.getCell('F9').numFmt = '# ?/?%';
ws.getRow(9).commit();

ws.getCell('A10').value = '<';
ws.getCell('B10').value = '>';
ws.getCell('C10').value = '<a>';
ws.getCell('D10').value = '><';
ws.getRow(10).commit();

ws.getRow(11).height = 40;
_.each(alignments, function(alignment, index) {
  var rowNumber = 11;
  var colNumber = index + 1;
  var cell = ws.getCell(rowNumber, colNumber);
  cell.value = alignment.text;
  cell.alignment = alignment.alignment;
});
ws.getRow(11).commit();

var row12 = ws.getRow(12);
row12.height = 40;
row12.getCell(1).value = 'Blue White Horizontal Gradient';
row12.getCell(1).fill = fills.blueWhiteHGrad;
row12.getCell(2).value = 'Red Dark Vertical';
row12.getCell(2).fill = fills.redDarkVertical;
row12.getCell(3).value = 'Red Green Dark Trellis';
row12.getCell(3).fill = fills.redGreenDarkTrellis;
row12.getCell(4).value = 'RGB Path Gradient';
row12.getCell(4).fill = fills.rgbPathGrad;

// row and column styles
ws.getRow(13).font = fonts.arialBlackUI14;
ws.getCell('H12').value = 'Foo';
ws.getCell('G13').value = 'Foo';
ws.getCell('H13').value = 'Bar';
ws.getCell('I13').value = 'Baz';
ws.getCell('H14').value = 'Baz';
//ws.getRow(13).commit();

wb.commit()
  .then(function(){
    console.log('Done.');
  })
  .catch(function(error) {
    console.log(error.message);
  });
